package webservices;

import interfaces.IMessageQuery;
import interfaces.ISlideQuery;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import webservices.helper.Helper;

public class MessageQueryImpl implements IMessageQuery {

	@Override
	public String query(String courseID) {
		String returnMessage = "";
		Connection con = null;
		try {
			con = Helper.getDBConnection();
			
			returnMessage = "<response name='success' />";
			ResultSet rs = null;
			rs = con.createStatement().executeQuery("select m.messageid, m.subject, m.content, m.createtime, u.name from message m, user u where m.userid = u.userid and courseid = " + courseID
													+ " order by m.createtime desc;");

			while (rs.next()){
				returnMessage += "<response name='message'>";
				returnMessage += "<messageID>" + rs.getString(1) + "</messageID>";
				returnMessage += "<subject>" + rs.getString(2) + "</subject>";
				returnMessage += "<content>" + rs.getString(3) + "</content>";
				returnMessage += "<createTime>" + rs.getString(4) + "</createTime>";
				returnMessage += "<userName>" + rs.getString(5) + "</userName>";
				returnMessage += "</response>";
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try{
				con.close();
			}catch(SQLException e) {}
		}
		return returnMessage;
	}
}
